package com.exam.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.exam.entity.Classes;
import com.exam.entity.User;
import com.exam.util.DBUtil;


public class ClassDao {
	
	//查询
	public List<Classes> sel(int page) throws SQLException {
		Connection conn=DBUtil.getConnection();
		int temp  = (page-1)*10;
		List<Classes> list=new ArrayList<Classes>();
		String sql="select * from classes limit ?,10";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[1];
		param[0]=temp;
		list =runner.query(conn,sql,param,new BeanListHandler<Classes>(Classes.class));
		conn.close(); 
		return list;
	}
	
	//班级注册
	public int registerClass(Classes classes) throws SQLException {
        boolean flag = false;
        Connection conn=DBUtil.getConnection();
        String sql="insert into classes(class_id,class_name, class_create, class_remark, class_state) values (?,?,?,?,?)";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[5];
		param[0]=classes.getClass_id();
		param[1]=classes.getClass_name();
		param[2]=classes.getClass_create();
		param[3]=classes.getClass_remark();
		param[4]=classes.getClass_state();
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	//通过classid查class名
	public String selClasses(int class_id ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT class_name FROM classes WHERE class_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(class_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		String class_name=null;
		while(rs.next()){
			class_name=rs.getString(1);
		}		
		return class_name;
	}
	
	//通过classid查用户
	public Classes selClass(int classid ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		//List<User> list=new ArrayList<User>();
		String sql="SELECT * FROM classes where class_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(classid);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		Classes classes = null;
		while(rs.next()){
			int class_id=rs.getInt(1);
			String class_name=rs.getString(2);
			Date class_create=rs.getDate(3);
			String class_remark=rs.getString(4);
			int class_state=rs.getInt(5);
			classes=new Classes(class_id, class_name, class_create, class_remark, class_state);
		}
		
		conn.close();
		return classes;
	}
	//修改班级
	public int modifyClass(Classes classes) throws SQLException {
        Connection conn=DBUtil.getConnection();
        String sql="UPDATE classes SET class_name=?, class_create=?,class_remark=?, class_state=? where class_id=?";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[5];
		param[0]=classes.getClass_name();
		param[1]=classes.getClass_create();
		param[2]=classes.getClass_remark();
		param[3]=classes.getClass_state();
		param[4]=classes.getClass_id();
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	
	//查出所有用户 --要分页查询
	public List show(int page,int id) throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		int temp  = (page-1)*10;
		String sql="select * from user where class_id =? and role_id =3 limit ?,10";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[2];
		param[0]=id;
		param[1]=temp;
		List list =runner.query(conn,sql,param,new BeanListHandler<User>(User.class));
		conn.close(); 
		return list;
	}
		
	// 班级学生的总人数
	public int count(int classid) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="select count(*) from user where class_id =?";
		QueryRunner runner = new QueryRunner();
		Long n  = runner.query(conn, sql,classid, new ScalarHandler<Long>());
		conn.close(); 
		return n.intValue();
	}
	// 班级总数
	public int count() throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="select count(*) from classes";
		QueryRunner runner = new QueryRunner();
		Long n  = runner.query(conn, sql, new ScalarHandler<Long>());
		conn.close(); 
		return n.intValue();
	}
	//通过id删除
	public int del(int uid ) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="delete from classes where class_id =?";	 
		QueryRunner runner = new QueryRunner();
		int n = runner.update(conn, sql, uid);
		conn.close();
		return n;
	}
	
}
